﻿using System;
using System.ComponentModel;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using Microsoft.SharePoint;
using Microsoft.SharePoint.WebControls;
using DevSky.SharePoint.Core.Controls;
using System.Web.UI.HtmlControls;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using DevSky.WebParts.DBConnectWP;
using System.IO;
using DevSky.WebParts.Resourcen;

namespace DevSky.WebParts
{
  [ToolboxItemAttribute(false)]
  public partial class DevSkyDBConnectWP : Microsoft.SharePoint.WebPartPages.WebPart, IPostBackEventHandler
  {
    const string RESULTGRIDMENUTEMPLATEID = "ResultGridMenuTemplateID";
    #region CONTROLS
    private SPGridViewAdvanced gvResult = null;
    private UpdatePanel pnlResult = null;
    private UpdateProgress upTaskPanelProgress = null;
    private Panel pnlContainer = null;
    private DropDownList cboGroupField = null;
    private DropDownList cboPageCount = null;
    private SPGridViewPager pager = null;
    #endregion

    #region
    public bool IsErrorOnRendering { get; set; }
    #endregion

    protected void Page_Load(object sender, EventArgs e)
    {

    }

    #region LAYOUT
    private Panel GetPanelGroupChoice()
    {
      Panel panel = new Panel() { ID = "pnlGrouping" };
      panel.Attributes.CssStyle.Add("margin-top", "5px");
      panel.Attributes.CssStyle.Add("margin-bottom", "4px");
      panel.Controls.Add(new Label { Text = "Gruppierung nach:", ID = "lblGroupBy" });
      cboGroupField.Attributes.CssStyle.Add("margin-left", "4px");
      panel.Controls.Add(cboGroupField);
      return panel;
    }

    private Panel GetPanelPageSize()
    {
      Panel panel = new Panel() { ID = "pnlPageSize" };
      panel.Attributes.CssStyle.Add("margin-top", "5px");
      panel.Attributes.CssStyle.Add("margin-bottom", "4px");
      panel.Controls.Add(new Label { Text = "Anzahl Treffer auf einer Seite:", ID = "lblPageCount" });
      cboPageCount.Attributes.CssStyle.Add("margin-left", "4px");
      panel.Controls.Add(cboPageCount);
      return panel;
    }

    private HtmlTable HeaderTable()
    {
      HtmlTable table = new HtmlTable();
      table.CellPadding = 0;
      table.CellSpacing = 0;
      table.Width = "100%";
      HtmlTableRow row = new HtmlTableRow();
      HtmlTableCell cell = new HtmlTableCell();
      cell.Align = "left";
      cell.Controls.Add(GetPanelGroupChoice());
      row.Cells.Add(cell);

      cell = new HtmlTableCell();
      cell.Align = "right";
      cell.Controls.Add(GetPanelPageSize());
      row.Cells.Add(cell);

      table.Rows.Add(row);

      return table;
    }
    #endregion

    #region overrides
    protected override void OnInit(EventArgs e)
    {
      base.OnInit(e);

      ClientScriptManager csm = Page.ClientScript;
      csm.RegisterClientScriptResource(this.GetType(), "DevSky.WebParts.JavaScript.devskydbwp.js");

      if (IncludeDefaultCSS)
      {
        HtmlLink cssLink = new HtmlLink();
        cssLink.Href = "/_layouts/styles/DevSkySolutions/core.css";
        cssLink.Attributes.Add("rel", "stylesheet");
        cssLink.Attributes.Add("type", "text/css");
        this.Page.Header.Controls.Add(cssLink);
      }
      gvResult = new SPGridViewAdvanced() { ID = "DevSkyResultGrid" };
      gvResult.PageIndexChanging += new GridViewPageEventHandler(gvResult_PageIndexChanging);

      cboGroupField = new DropDownList() { ID = "cboGroupField", AutoPostBack = true };
      cboGroupField.SelectedIndexChanged += new EventHandler(cboGroupField_SelectedIndexChanged);

      cboPageCount = new DropDownList() { ID = "cboPageCount", AutoPostBack = true };
      cboPageCount.SelectedIndexChanged += new EventHandler(cboPageCount_SelectedIndexChanged);

      pager = new SPGridViewPager() { ID = "GridResultPager", GridViewId = gvResult.ID };

      pnlResult = new UpdatePanel { ID = "upGridViewResult", ChildrenAsTriggers = false };
      pnlResult.UpdateMode = UpdatePanelUpdateMode.Conditional;
      pnlResult.Triggers.Add(new AsyncPostBackTrigger { ControlID = pager.ID });
      pnlResult.Triggers.Add(new AsyncPostBackTrigger { ControlID = gvResult.ID });
      pnlResult.Triggers.Add(new AsyncPostBackTrigger { ControlID = cboGroupField.ID });
      pnlResult.Triggers.Add(new AsyncPostBackTrigger { ControlID = cboPageCount.ID });

      upTaskPanelProgress = new UpdateProgress() { ID = "upSearchPanelProgress" };
      upTaskPanelProgress.AssociatedUpdatePanelID = pnlResult.ID;
      upTaskPanelProgress.ProgressTemplate = new ProgressLoadingTemplate(LoadingWaitImagePath, LoadingWaitText);

      pnlContainer = new Panel() { ID = "pnlContainer" };
    }

    protected override void OnLoad(EventArgs e)
    {
      if (!IsErrorOnRendering)
      {
        try
        {
          base.OnLoad(e);
          this.EnsureChildControls();
          if (Page.IsPostBack)
          {
            gvResult.DataBind();
          }
        }
        catch (Exception ex)
        {
          HandleException(ex);
        }
      }
      IsErrorOnRendering = false;
    }
    #endregion

    #region EVENTS
    void gvResult_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
      gvResult.PageIndex = e.NewPageIndex;
      gvResult.DataBind();
    }

    void ds_ObjectCreating(object sender, ObjectDataSourceEventArgs e)
    {
      e.ObjectInstance = new DataTableWrapper(ReadData());
    }

    private DataTable ReadData()
    {
      DataTable table = null;
      SqlCommand cmd = null;
      SqlConnection cnn = new SqlConnection(ConnectString);
      try
      {
        if (DataSourceType.Equals("SP"))
          cmd = new SqlCommand(DataSource, cnn) { CommandType = CommandType.StoredProcedure };
        if (DataSourceType.Equals("TABLE") || DataSourceType.Equals("VIEW"))
          cmd = new SqlCommand(DBTools.FullSelectStatement(CustomDataColumns, DataSource), cnn) { CommandType = CommandType.Text };
        if (cmd != null)
        {
          cnn.Open();
          using (SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd))
          {
            table = new DataTable();
            sqlAdapter.Fill(table);
          }
        }

        if (cboGroupField.Items.Count > 0)
          table.Columns.Add(new DataColumn("AllRecords", typeof(string)));
      }
      catch (Exception)
      {
      }
      finally
      {
        cnn.Close();
        cnn.Dispose();
      }
      return table;
    }

    void cboPageCount_SelectedIndexChanged(object sender, EventArgs e)
    {
      ListItem item = cboPageCount.SelectedItem;
      if (item != null)
      {
        int pageSize = 0;
        if (Int32.TryParse(item.Value, out pageSize))
        {
          gvResult.PageSize = pageSize;
          gvResult.PageIndex = 0;
          gvResult.DataBind();
        }
      }
    }

    void cboGroupField_SelectedIndexChanged(object sender, EventArgs e)
    {
      ListItem item = (sender as DropDownList).SelectedItem;
      //if (item.Value.Length > 0)
      {
        gvResult.GroupField = item.Value;
        gvResult.PageIndex = 0;
        gvResult.Sort(gvResult.GroupField, SortDirection.Ascending);
        gvResult.DataBind();
      }
    }
    #endregion

    protected override void CreateChildControls()
    {
      if (!IsErrorOnRendering)
      {
        try
        {
          base.CreateChildControls();
          Controls.Clear();
          gvResult = InitResultGrid(gvResult);

          if (ConnectionStringValid && ColumnConfigurationValid)
          {
            #region MENU
            //if (AddMenuToGridEntries)
            // Create always the Menu and switch the visibilty to prevent viewstate exceptions
            CreateMenuItems();
            #endregion
            string filterFields = string.Empty;
            string uniqueIdentifierColumnName = string.Empty;
            gvResult = InitResultGridColumns(gvResult, out filterFields);
            if (cboGroupField.Items.Count > 0)
            {
              cboGroupField.Items.Insert(0, new ListItem("All", "ALLRECORDS"));
              gvResult.AllowGrouping = true;
              gvResult.GroupField = "AllRecords";
              gvResult.GroupFieldDisplayName = "All Records";
              gvResult.GridLines = GridLines.Horizontal;
              gvResult.AllowGroupCollapse = true;
            }
            else
              cboGroupField.Visible = false;

            if (!string.IsNullOrEmpty(filterFields) && ShowGridHeader)
            {
              gvResult.AllowFiltering = true;
              gvResult.FilterDataFields = filterFields;
              gvResult.FilteredDataSourcePropertyName = "FilterExpression";
              gvResult.FilteredDataSourcePropertyFormat = "{1} like '{0}'";
            }

            #region DataSource
            ObjectDataSource ds = new ObjectDataSource();
            ds.ID = "odsDataSource";
            ds.TypeName = typeof(DataTableWrapper).AssemblyQualifiedName;
            ds.SelectMethod = "GetTable";
            ds.ObjectCreating += new ObjectDataSourceObjectEventHandler(ds_ObjectCreating);
            this.Controls.Add(ds);
            gvResult.DataSourceID = "odsDataSource";

            #endregion
          }

          #region Seitentreffer
          cboPageCount.Items.Clear();
          cboPageCount.Items.Add(new ListItem("10", "10"));
          cboPageCount.Items.Add(new ListItem("25", "25"));
          cboPageCount.Items.Add(new ListItem("50", "50"));
          cboPageCount.Items.Add(new ListItem("75", "75"));
          cboPageCount.Items.Add(new ListItem("100", "100"));
          #endregion


          #region Add controls to control tree
          pnlContainer.Controls.Add(HeaderTable());
          pnlContainer.Controls.Add(gvResult);

          pnlResult.ContentTemplateContainer.Controls.Add(pnlContainer);
          pnlResult.ContentTemplateContainer.Controls.Add(pager);
          Controls.Add(upTaskPanelProgress);
          Controls.Add(pnlResult);
          #endregion
          // Load data

          gvResult.DataBind();
        }
        catch (Exception ex)
        {
          HandleException(ex);
        }
      }
      IsErrorOnRendering = false;
    }

    private SPGridViewAdvanced InitResultGridColumns(SPGridViewAdvanced gvResult, out string filterFields)
    {
      gvResult.Columns.Clear();
      filterFields = string.Empty;
      if (CustomDataColumns != null)
      {
        BoundField colName = null;
        foreach (CustomDataColumn column in CustomDataColumns.Where(col => col.IsVisible).OrderBy(col => col.Position))
        {
          if (gvResult.Columns.Count == 0 && AddMenuToGridEntries)
          {
            string uniqueIdentifierColumnName = (from cdc in CustomDataColumns where cdc.IsUniqueColumn select cdc.Name).FirstOrDefault();
            gvResult.Columns.Add(BuildGridMenu(column, uniqueIdentifierColumnName));
          }
          else
          {
            colName = new BoundField();
            if (column.IsLookupColumn)
            {
              colName.DataField = column.Lookup.DisplayField;
              colName.SortExpression = column.Lookup.DisplayField;
            }
            else
            {
              colName.DataField = column.Name;
              colName.SortExpression = column.Name;
            }
            colName.HeaderText = string.IsNullOrEmpty(column.DisplayName) ? column.Name : column.DisplayName;
            gvResult.Columns.Add(colName);
          }
          if (column.IsGroupable)
            cboGroupField.Items.Add(new ListItem(string.IsNullOrEmpty(column.DisplayName) ? column.Name : column.DisplayName, column.Name));

          // TODO: Filter on other than text data types
          if (column.IsFilterable)
          {
            filterFields += (filterFields.EndsWith(",") ? string.Empty : ",") + (column.IsLookupColumn ? column.Lookup.DisplayField : column.Name);
          }
          else
            filterFields += ",";

        }
      }
      return gvResult;
    }

    private SPGridViewAdvanced InitResultGrid(SPGridViewAdvanced gvResult)
    {
      gvResult.AutoGenerateColumns = false;
      gvResult.Enabled = true;
      gvResult.ShowHeader = ShowGridHeader;
      gvResult.Width = Unit.Percentage(100);
      gvResult.AllowGrouping = false;
      gvResult.AllowPaging = true;
      gvResult.AllowSorting = AllowSortingOnAllCoumns;
      gvResult.AllowFiltering = false;
      gvResult.DisplayGroupFieldName = false;
      gvResult.EmptyDataText = NoDataMessage;
      gvResult.PageSize = 10; // TODO MAZ CONFIG
      gvResult.PageIndex = 0;
      return gvResult;
    }

    // Menu will be added to the first visible column be default - later customizable
    private SPMenuField BuildGridMenu(CustomDataColumn column, string uniqueColumnName)
    {
      SPMenuField menuColumn = new SPMenuField();
      menuColumn.MenuTemplateId = RESULTGRIDMENUTEMPLATEID;
      menuColumn.HeaderText = string.IsNullOrEmpty(column.DisplayName) ? column.Name : column.DisplayName;
      if (column.IsLookupColumn)
      {
        menuColumn.TextFields = column.Lookup.DisplayField;
        menuColumn.SortExpression = column.Lookup.DisplayField;
      }
      else
      {
        menuColumn.TextFields = column.Name;
        menuColumn.SortExpression = column.Name;
      }
      // nur möglich wenn erlaubte Protokolle überschrieben wird. 
      //menuColumn.NavigateUrlFields = column.Name;
      //menuColumn.NavigateUrlFormat = "javascript:alert('{0}');";
      if (!string.IsNullOrEmpty(uniqueColumnName))
        menuColumn.TokenNameAndValueFields = "TOKEN1=" + uniqueColumnName;
      return menuColumn;
    }

    private void HandleException(Exception ex)
    {
      this.IsErrorOnRendering = true;
      this.Controls.Clear();
      this.Controls.Add(new LiteralControl(ex.Message));
    }

    public bool ConnectionStringValid
    {
      get
      {
        return true;
      }
    }

    public bool ColumnConfigurationValid
    {
      get
      {
        return (CustomDataColumns != null && CustomDataColumns.Count > 0);
      }
    }

    public bool AddMenuToGridEntries
    {
      get
      {
        return (ShowReadOnlyLink || ShowNewLink || ShowEditLink || ShowDeleteLink || ShowCustomLinks);
      }
    }

    public void RefreshDataGrid()
    {
      gvResult.PageIndex = 0;
      if (CustomDataColumns != null)
        gvResult.DataBind();
      else
        gvResult.Columns.Clear();
    }

    private void CreateMenuItems()
    {
      // TODO: Add SharePoint security here ...
      MenuTemplate menuTemplate = new MenuTemplate();
      menuTemplate.ID = RESULTGRIDMENUTEMPLATEID;

      MenuItemTemplate menuItem = new MenuItemTemplate(WPStrings.GRDMNU_ReadOnly, "/_layouts/images/DevSkySolutions/grdmnu_read.png");
      menuItem.ClientOnClickScript = "OpenEntryReadyOnlyMode('" + this.UniqueID + "', '%TOKEN1%')";
      menuTemplate.Controls.Add(menuItem);
      menuItem.Visible = ShowReadOnlyLink;

      menuItem = new MenuItemTemplate(WPStrings.GRDMNU_EditItem, "/_layouts/images/DevSkySolutions/grdmnu_edit.png");
      menuItem.ClientOnClickScript = "OpenEntryEditMode('" + this.UniqueID + "', '%TOKEN1%')";
      menuTemplate.Controls.Add(menuItem);
      menuItem.Visible = ShowEditLink;

      menuItem = new MenuItemTemplate(WPStrings.GRDMNU_NewItem, "/_layouts/images/DevSkySolutions/grdmnu_new.png");
      menuTemplate.Controls.Add(menuItem);
      menuItem.ClientOnClickScript = "CreateEntry('" + this.UniqueID + "', '%TOKEN1%')";
      menuItem.Visible = ShowNewLink;

      menuItem = new MenuItemTemplate(WPStrings.GRDMNU_DeleteItem, "/_layouts/images/DevSkySolutions/grdmnu_delete.png");
      menuTemplate.Controls.Add(menuItem);
      menuItem.ClientOnClickScript = "DeleteEntry('" + this.UniqueID + "', '%TOKEN1%')";
      menuItem.Visible = ShowDeleteLink;

      /*
      menuTemplate.Controls.Add(new MenuSeparatorTemplate());

      menuItem = new MenuItemTemplate("Öffne Antragsformular", "/_layouts/images/icon_survey.gif");
      menuItem.ClientOnClickNavigateUrl = "%TOKEN7%?XmlLocation=%TOKEN5%&Source=%TOKEN6%&DefaultItemOpen=1";
      menuTemplate.Controls.Add(menuItem);

      menuItem = new MenuItemTemplate("Gehe zur Antragsbibliothek", "/_layouts/images/TFBULLST.gif");
      menuItem.ClientOnClickNavigateUrl = "%TOKEN4%";
      menuTemplate.Controls.Add(menuItem);*/


      pnlContainer.Controls.Add(menuTemplate);
    }

    // http://blog.avanadeadvisor.com/blogs/johanr/archive/2008/06/11/11348.aspx
    private MENUACTION selectedAction = MENUACTION.UNDEFINED;
    private string selectedMenuValue = string.Empty;
    public void RaisePostBackEvent(string eventArgument)
    {
      selectedAction = MENUACTION.UNDEFINED;
      if ((!string.IsNullOrEmpty(eventArgument)) && eventArgument.StartsWith("ID:"))
      {
        selectedAction = MENUACTION.SELECTION;
        //selectedMenuValue = eventArgument.Split(new char[] { ':' })[1].ToString();

      }
      if ((!string.IsNullOrEmpty(eventArgument)) && eventArgument.StartsWith("DELETE:"))
      {
        selectedMenuValue = eventArgument.Split(new char[] { ':' })[1].ToString();
        DeleteEntry(selectedMenuValue);
      }
    }

    private void DeleteEntry(string selectedMenuValue)
    {
      CustomDataColumn dc = (from dataColumn in CustomDataColumns where dataColumn.IsUniqueColumn select dataColumn).FirstOrDefault();
      if (dc != null)
      {
        SqlCommand sqlCMD = new SqlCommand();
        sqlCMD.CommandText = string.Format("DELETE FROM {0} Where {1} = {2}", DataSource, dc.Name, selectedMenuValue);
        SqlConnection cnn = new SqlConnection(ConnectString);
        int execResult = 0;
        try
        {
          cnn.Open();
          sqlCMD.Connection = cnn;
          execResult = sqlCMD.ExecuteNonQuery();
        }
        catch (Exception exc)
        {
          Page.ClientScript.RegisterStartupScript(GetType(), "ErrorMessage", "alert(" + Tools.EscapeJSMssages(string.Format("Cannot delete entry. Please consider the following message:\n{0}", exc.Message)) + ");", true);
        }
        finally
        {
          cnn.Close();
        }
      }
    }

    /*private void WriteVariables()
    {
        if (!Page.ClientScript.IsClientScriptBlockRegistered("VARIABLES"))
            Page.ClientScript.RegisterClientScriptBlock(typeof(DevSkyDBConnectWP), "VARIABLES", "var ctlRereshID = '" + pnlResult.ClientID + "'", true);
    }*/
  }
}
